﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Dynamic;
using System.Globalization;
using System.Linq;
using System.Text;
using WebMatrix.Data;

namespace SplendidCRM.Ext
{
    public class Query : IDisposable
    {
        private Database _database;
        private static readonly String ConnectionString;

        static Query()
        {
            ConnectionString = Manager.Site.ConnectionString;
        }

        private DbTransaction _transaction;

        public void Begin()
        {
            Rollback();
            EnsureConnectionIsOpen();
            _transaction = Connection.BeginTransaction();
        }

        public void Commit()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                _transaction = null;
            }
        }

        public void Rollback()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
            }
        }

        public DbConnection Connection
        {
            get { return GetDatabase().Connection; }
        }

        private Database GetDatabase()
        {
            if (_database != null) return _database;
            return _database = Database.OpenConnectionString(ConnectionString);
        }

        public IEnumerable<dynamic> Multiple(String sql, params Object[] parameters)
        {
            return Limit(0, sql, parameters);
        }

        public IEnumerable<dynamic> Limit(int count, string sql, params object[] parameters)
        {
            return Limit(0, count, sql, parameters);
        }

        public IEnumerable<dynamic> Limit(int start, int count, string sql, params object[] parameters)
        {
            Int32 total;
            return Limit(start, count, out total, sql, parameters);
        }



        public IEnumerable<dynamic> Limit(int start, int count, out int total, string sql, params object[] parameters)
        {
            var command = GetCommand(sql, parameters);
            command.CommandText += " set @totalRecords = @@ROWCOUNT";
            var totalParameter = command.CreateParameter();
            totalParameter.ParameterName = "totalRecords";
            totalParameter.Direction = ParameterDirection.Output;
            totalParameter.DbType = DbType.Int32;
            totalParameter.Value = 0;
            command.Parameters.Add(totalParameter);
            IEnumerable<dynamic> results;
            using (command)
            {
                if (command is SqlCommand)
                {
                    var adapter = new SqlDataAdapter((SqlCommand) command);
                    var dataTable = new DataTable("Source");
                    adapter.Fill(start, count, dataTable);
                    using (var reader = dataTable.CreateDataReader())
                    {
                        results = GetRecords(reader, 0, 0, dataTable).ToArray();
                    }
                }
                else
                {
                    using (var reader = command.ExecuteReader())
                    {
                        results = GetRecords(reader, start, count).ToArray();
                    }
                }
                total = (Int32) totalParameter.Value;
            }
            EnsureConnectionIsClose();
            return results;
        }

        private IEnumerable<dynamic> GetRecords(DbDataReader reader, Int32 start, Int32 count, DataTable table = null)
        {
            String[] columnNames = null;
            var index = 0;
            var readRecords = 0;
            foreach (DbDataRecord record in reader)
            {
                if (start > 0 && start < index) continue;
                if (columnNames == null) columnNames = GetColumnNames(record).ToArray();
                yield return new Result(columnNames, record, table);
                index++;
                readRecords++;
                if (count > 0 && readRecords >= count) break;
            }
        }

        public Int32 Delete(String table)
        {
            return Execute(string.Format("delete {0}", table));
        }

        public Int32 Delete(String table, String condition, params Object[] parameters)
        {
            return Execute(String.Format("delete {0} where {1}", table, condition), parameters);
        }

        private IEnumerable<string> GetColumnNames(DbDataRecord record)
        {
            // Get all of the column names for this query 
            for (int i = 0; i < record.FieldCount; i++)
            {
                yield return record.GetName(i);
            }
        }

        private static String GetConditionAndParameters<TObject>(TObject obj, ICollection<Object> parameters, IEnumerable<Func<TObject, Object>> conditions)
        {
            var builder = new StringBuilder();
            foreach (var condition in conditions)
            {
                if (builder.Length > 0) builder.Append(" and ");
                builder.AppendFormat("({0} = @{1})", condition.Method.GetParameters()[0].Name, parameters.Count);
                parameters.Add(condition(obj));
            }
            return builder.ToString();
        }

        public int Update<TObject>(TObject values, string table, params Func<TObject, object>[] conditions)
        {
            var parameters = new List<Object>();
            return Update(table, values, GetConditionAndParameters(values, parameters, conditions), parameters.ToArray());
        }

        public static void AddPropertyParameters(Object target, ICollection<Object> parameters, Action<String, Int32> parameterEvaluator)
        {
            if (target is IDictionary<String, Object>)
            {
                var dic = (IDictionary<String, Object>)target;
                foreach(var pair in dic)
                {
                    var index = parameters.Count;
                    parameters.Add(pair.Value);
                    parameterEvaluator(pair.Key, index);
                }
            }
            else
            {
                var properties = TypeDescriptor.GetProperties(target);
                foreach (PropertyDescriptor p in properties)
                {
                    var value = p.GetValue(target) ?? new Param {Type = p.PropertyType, Value = DBNull.Value};
                    var index = parameters.Count;
                    parameters.Add(value);
                    parameterEvaluator(p.Name, index);
                }
            }
        }

        public Int32 Update(String table, Object values, String condition, params Object[] parameters)
        {
            if (String.IsNullOrEmpty(table)) throw new ArgumentNullException("table");
            if (values == null) throw new ArgumentNullException("values");
            var p = new List<Object>(parameters);
            var isFirst = true;
            var builder = new StringBuilder();
            builder.AppendFormat("update {0} set ", table);
            AddPropertyParameters(values, p, (n, i) =>
                                                          {
                                                              if (!isFirst) builder.Append(",");
                                                              builder.AppendFormat("[{0}] = @{1}", n, i);
                                                              isFirst = false;
                                                          });
            if (isFirst) return 0;
            builder.AppendFormat(" where {0} ", condition);

            return Execute(builder.ToString(), p.ToArray());
        }

        private static String BuildInsertStatement(String table, IEnumerable<Object> objs, ICollection<Object> parameters)
        {
            if (String.IsNullOrEmpty(table)) throw new ArgumentNullException("table");
            var builder = new StringBuilder();
            foreach (var o in objs)
            {
                if (o == null) continue;
                var fieldNames = new List<String>();
                var fieldValues = new List<String>();

                AddPropertyParameters(o, parameters, (n, i) =>
                                                         {
                                                             fieldNames.Add("[" + n + "]");
                                                             fieldValues.Add("@" + i);
                                                         });

                if (fieldNames.Count == 0) continue;
                builder.AppendFormat("insert into {0}({1}) values({2})", table, String.Join(",", fieldNames.ToArray()), String.Join(",", fieldValues.ToArray()));
            }
            return builder.ToString();
        }

        public Int32 Insert(String table, params Object[] objs)
        {
            var parameters = new List<Object>();
            return Execute(BuildInsertStatement(table, objs, parameters), parameters.ToArray());
        }

        /// <summary>
        /// Insert object and return new id
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public Int64 Insert(Object obj, String table)
        {
            var parameters = new List<Object>();
            return Value<Int64>(BuildInsertStatement(table, new[] {obj}, parameters), parameters.ToArray());
        }

        private IEnumerable<DbParameter> GetParameters(DbCommand command, Object value, Int32[] indexes)
        {
            IDictionary<String, Object> dic = null;
            var isDic = false;
            if (value != null && !(value is Param))
            {
                var type = value.GetType();
                if (type.IsClass && !type.IsArray && !type.IsValueType && type != typeof(String) && type != typeof(DateTime))
                {
                    if (value is IDictionary<String, Object>)
                    {
                        dic = Mvc.GetRouteValues(value);
                    }
                    else
                    {
                        var properties = TypeDescriptor.GetProperties(value);
                        dic = new Dictionary<String, Object>(StringComparer.OrdinalIgnoreCase);
                        foreach (PropertyDescriptor property in properties)
                        {
                            dic[property.Name] = new Param {Type = property.PropertyType, Value = property.GetValue(value)};
                        }
                    }
                    isDic = true;
                }
            }

            if (dic == null)
            {
                dic = new Dictionary<string, Object>(StringComparer.OrdinalIgnoreCase);
                dic[indexes.ToString()] = value;
            }


            foreach (var p in dic)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = isDic ? p.Key : indexes[0].ToString(CultureInfo.InvariantCulture);
                if (p.Value is Param)
                {
                    var param = (Param)p.Value;
                    var dbType = DbType.Object;
                    TypeCode code;
                    var type = param.Type;


                    if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        type = type.GetGenericArguments()[0];
                    }
                    else if (type == typeof(Object) && param.Value != null)
                    {
                        type = param.Value.GetType();
                    }
                    if (type == typeof(Guid))
                    {
                        dbType = DbType.Guid;
                        code = TypeCode.Object;
                    }
                    else
                    {
                        code = Type.GetTypeCode(type);
                    }
                    parameter.DbType = dbType != DbType.Object ? dbType : (DbType) Enum.Parse(typeof (DbType), code.ToString());
                    parameter.Value = param.Value ?? DBNull.Value;
                }
                else
                {
                    parameter.Value = p.Value;
                }
                yield return parameter;
                if (!isDic) indexes[0]++;
            }
        }

        private void AddParameters(DbCommand command, IEnumerable<Object> args)
        {
            if (args == null)
            {
                return;
            }

            // Create numbered parameters 

            var indexes = new[] {0};
            var parameters = args.SelectMany(o => GetParameters(command, o, indexes));

            foreach (var p in parameters)
            {
                command.Parameters.Add(p);
            }
        }

        public dynamic Single(String sql, params Object[] parameters)
        {
            return Limit(1, sql, parameters).FirstOrDefault();
        }

        private void EnsureConnectionIsOpen()
        {
            if (Connection.State != ConnectionState.Closed) return;
            Connection.Open();
        }

        private void EnsureConnectionIsClose()
        {
            try
            {
                Connection.Close();
            }
            catch (Exception)
            {
            }
        }

        public Object Value(String sql, params Object[] parameters)
        {
            return GetCommand(sql, parameters).ExecuteScalar();
        }

        public T Value<T>(String sql, params Object[] parameters)
        {
            return Value<T>(sql, null, parameters);
        }

        public T Value<T>(String sql, Func<Object, T> converter, params Object[] parameters)
        {
            var result = Value(sql, parameters);
            EnsureConnectionIsClose();
            var fallback = true;
            try
            {
                if (converter == null)
                {
                    fallback = false;
                    return (T) Convert.ChangeType(result, typeof (T));
                }
                return converter(result);
            }
            catch (Exception)
            {
                try
                {
                    if (fallback) return (T) Convert.ChangeType(result, typeof (T));
                }
                catch (Exception)
                {
                }
            }
            return default(T);
        }

        public Int32 Execute(String sql, params Object[] parameters)
        {
            return GetCommand(sql, parameters).ExecuteNonQuery();
        }

        private DbCommand GetCommand(String sql, params Object[] parameters)
        {
            EnsureConnectionIsOpen();
            var command = Connection.CreateCommand();
            command.CommandText = sql;
            command.CommandType = CommandType.Text;
            AddParameters(command, parameters);
            if (sql.StartsWith("exec:", StringComparison.OrdinalIgnoreCase))
            {
                command.CommandText = "exec " + sql.Substring(5);
                var first = true;
                foreach (DbParameter p in command.Parameters)
                {
                    if (!first) command.CommandText += ",";
                    Int32 index;
                    if (Int32.TryParse(p.ParameterName, out index))
                    {
                        command.CommandText += " @" + p.ParameterName;
                    }
                    else
                    {
                        command.CommandText += " @" + p.ParameterName + "=@" + p.ParameterName;
                    }
                    first = false;
                }
            }
            if (_transaction != null) command.Transaction = _transaction;
            
            return command;
        }

        #region Implementation of IDisposable

        public void Dispose()
        {
            Rollback();
            EnsureConnectionIsClose();
            if (_database != null) _database.Dispose();
        }

        #endregion

        internal class Result : DynamicObject, IDictionary<String, Object>
        {
            public Result(IEnumerable<String> columns, IDataRecord record, DataTable table)
            {
                if(table != null)
                {
                    foreach (var c in columns)
                    {
                        var value = record[c];
                        if (value is DBNull)
                        {
                            var column = table.Columns[c];
                            value = column.DataType.IsValueType ? Activator.CreateInstance(column.DataType) : null;
                        }
                        _values[c] = value;
                    }
                }
                else
                {
                    foreach (var c in columns)
                    {
                        _values[c] = record[c];
                    }
                }
            }

            public Result(IEnumerable<String> columns, DynamicRecord record)
            {
                foreach (var c in columns)
                {
                    _values[c] = record[c];
                }
            }

            public Result(IEnumerable<KeyValuePair<string, object>> record)
            {
                foreach (var c in record)
                {
                    _values.Add(c);
                }
            }

            public override bool TryGetMember(GetMemberBinder binder, out object result)
            {
                _values.TryGetValue(binder.Name, out result);
                return true;
            }

            public override bool TrySetMember(SetMemberBinder binder, object value)
            {
                _values[binder.Name] = value;
                return true;
            }

            private readonly IDictionary<String, Object> _values = new Dictionary<String, Object>(StringComparer.OrdinalIgnoreCase);

            internal IDictionary<String, Object> Values
            {
                get { return _values; }
            }

            #region Implementation of IEnumerable

            IEnumerator<KeyValuePair<string, object>> IEnumerable<KeyValuePair<string, object>>.GetEnumerator()
            {
                return _values.GetEnumerator();
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return _values.GetEnumerator();
            }

            #endregion

            #region Implementation of ICollection<KeyValuePair<string,object>>

            void ICollection<KeyValuePair<string, object>>.Add(KeyValuePair<string, object> item)
            {
                _values.Add(item);
            }

            void ICollection<KeyValuePair<string, object>>.Clear()
            {
                _values.Clear();
            }

            bool ICollection<KeyValuePair<string, object>>.Contains(KeyValuePair<string, object> item)
            {
                return _values.Contains(item);
            }

            void ICollection<KeyValuePair<string, object>>.CopyTo(KeyValuePair<string, object>[] array, int arrayIndex)
            {
                _values.CopyTo(array, arrayIndex);
            }

            bool ICollection<KeyValuePair<string, object>>.Remove(KeyValuePair<string, object> item)
            {
                return _values.Remove(item);
            }

            int ICollection<KeyValuePair<string, object>>.Count
            {
                get { return _values.Count; }
            }

            bool ICollection<KeyValuePair<string, object>>.IsReadOnly
            {
                get { return _values.IsReadOnly; }
            }

            #endregion

            #region Implementation of IDictionary<string,object>

            bool IDictionary<string, object>.ContainsKey(string key)
            {
                return _values.ContainsKey(key);
            }

            void IDictionary<string, object>.Add(string key, object value)
            {
                _values.Add(key, value);
            }

            bool IDictionary<string, object>.Remove(string key)
            {
                return _values.Remove(key);
            }

            bool IDictionary<string, object>.TryGetValue(string key, out object value)
            {
                return _values.TryGetValue(key, out value);
            }

            object IDictionary<string, object>.this[string key]
            {
                get
                {
                    Object value;
                    _values.TryGetValue(key, out value);
                    return value;
                }
                set { _values[key] = value; }
            }

            ICollection<string> IDictionary<string, object>.Keys
            {
                get { return _values.Keys; }
            }

            ICollection<object> IDictionary<string, object>.Values
            {
                get { return _values.Values; }
            }

            #endregion
        }

        class Param
        {
            public Type Type { get; set; }
            public Object Value { get; set; }
        }
    }
}
